package cn.com.callback

import groovy.json.*;
import groovy.swing.impl.DefaultAction;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.RequestDispatcher;
import cn.com.elite.EliteBean;
import java.security.MessageDigest;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.wisdge.dataservice.Result;
import java.util.Map;
import java.util.Date;
import java.util.HashMap;
import cn.com.elite.Util;
import cn.com.kh.UcisXML;
import java.net.URLEncoder;

import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;

/**
 *
 * @Title: BigdateStaffToday
 * @Description:大数据定时更新每日人员数据
 * @author: Yuchieh 
 * @date 2021-07-13
 *
 */


public class BigdateStaffToday implements ServiceBackInvoke{
	private static final Log log=LogFactory.getLog(BigdateStaffToday.class);
	public String doCallBack(EliteBean eliteBean,HttpServletRequest request,HttpServletResponse response){
				
		response.setCharacterEncoding("UTF-8");
		JSONObject resulterror = new JSONObject();
				

		try{
			//将数据写进his表
			def todaysql_bak = "INSERT INTO bigdateStaff_his SELECT *,GETDATE() FROM bigdateStaff";
			//eliteBean.update(todaysql_bak, "dataSource");
			//清空表数据
			def todaysql_del = "TRUNCATE TABLE bigdateStaff";
			eliteBean.update(todaysql_del, "dataSource");
			//写入数据
			def todaysql_ins1 = "INSERT INTO bigdateStaff SELECT T.Area_desc,K.[组],K.[姓名],K.[分机号],(T.[呼入总量]+T.[呼出总量]) 通话总量,K.[状态],Abs(K.[状态持续时长]) '状态持续时长(s)',D.在线时长,D.示忙时长,D.示闲时长,D.回访时长,D.培训时长,D.事务与管理时长,D.帮助支持时长,D.开会时长,D.多媒体事务时长,D.小休时长, ((T.[呼入通话时长]+T.[呼出通话时长]) / (T.[呼入总量]+T.[呼出总量])) '平均通话时长(s)',a1.[平均话后时长] '平均话后时长(s)',T.[平均振铃时长] '平均振铃时长(s)',A.[小休率],A.[工作效率],isnull(W1.[转评价数],0) 转评价数,isnull(W2.[好评数],0) 好评数 FROM ( select ga.Area_desc,s.STAFFNAME,s.AGENT_ID,S.Staff_ID, SUM(case when CALLTYPE=1 and FirstAgentCDN is not null AND FirstAgentCDN <>'' and FirstAgentQueue is not null AND FirstAgentQueue <>'' and firstagent != '' then 1 else 0 end) 呼入总量, SUM(case when CALLTYPE=1 and FirstAgentCDN is not null AND FirstAgentCDN <>'' and FirstAgentQueue is not null AND FirstAgentQueue <>'' and firstagent != '' and firstagentqueueabandoned =0 and firstagentabandoned =0  then 1 else 0 end) 呼入接通量, SUM(case when CALLTYPE=1 and FirstAgentCDN is not null and FirstAgentCDN!='' and FirstAgentQueueAbandoned=0 and FirstAgentAbandoned=0 then DATEDIFF(ss,FirstRingingEndTime,callendtime) else 0 end) 呼入通话时长, SUM(case when CALLTYPE=4 then 1 else 0 end) 呼出总量, SUM(case when CALLTYPE=4 AND FirstAgentQueue <>'' and FirstAgentQueue is not null and firstagent != '' and FirstAgentQueueAbandoned =0 and FirstAgentAbandoned =0 and CallResult=1  then 1 else 0 end) 呼出接通量, SUM(case when CALLTYPE=4 and FirstAgentQueue <>'' and FirstAgentQueue is not null and firstagent != '' and FirstAgentQueueAbandoned =0 and FirstAgentAbandoned =0 and CallResult=1 then DATEDIFF(ss,FirstRingingEndTime,callendtime) else 0 end) 呼出通话时长, case when SUM(case when t1.CALLTYPE=1 and FirstAgentCDN is not null and FirstAgentCDN!='' and FirstAgentQueueAbandoned=0 and FirstAgentAbandoned=0 then 1 else 0 end)=0 then 0 else SUM(case when t1.CALLTYPE=1 and FirstAgentCDN is not null and FirstAgentCDN!='' and FirstAgentQueueAbandoned=0 and FirstAgentAbandoned=0 then DATEDIFF(ss,FirstRingingStartTime,FirstRingingEndTime) else 0 end)/SUM(case when t1.CALLTYPE=1 and FirstAgentCDN is not null and FirstAgentCDN!='' and FirstAgentQueueAbandoned=0 and FirstAgentAbandoned=0 then 1 else 0 end) end 平均振铃时长 from Call_Detail_view t1 WITH(NOLOCK) LEFT JOIN staff s on s.AGENT_ID=t1.FirstAgent LEFT JOIN RolegroupArea rga on rga.Group_id = s.DEFAULTROLEGROUP LEFT JOIN Group_Area ga on ga.Area_guid = rga.Area_guid where t1.CallStartTime>=CONVERT(varchar(10),GETDATE(),120) and s.STAFFNAME is not null  AND ga.area_id in ('JT','BA','LG','GM') group by ga.Area_desc,s.STAFFNAME,s.AGENT_ID,S.Staff_ID ) T  LEFT JOIN ( select asd.AgentID, 	sum(case when asd.status in (2,3,4) and vCID is not null then datediff(s,starttime,endtime) else 0 end ) as 在线时长, 	sum(case when asd.status = 4 and vCID is not null AND CallType = 1 then datediff(ss,starttime,endtime) else 0 end ) +  	sum(case when asd.status = 4 and vCID is not null AND CallType = 4 then datediff(ss,starttime,endtime) else 0 end ) +  	SUM(CASE WHEN asd.status = 3 THEN datediff(ss,starttime,endtime) ELSE 0 END )	as 示忙时长, 	sum(case when asd.status = 2 and vCID is not null then datediff(ss,starttime,endtime) else 0 end ) as 示闲时长,	SUM(Case when asd.reasoncode = '1' and asd.WorkMode = '3' and asd.Status='3' then datediff(second,starttime,endtime) else 0 end) as 回访时长, 	SUM(Case when asd.reasoncode = '3' and asd.WorkMode = '3' and asd.Status='3' then datediff(second,starttime,endtime) else 0 end) as 培训时长, 	SUM(Case when asd.reasoncode = '4' and asd.WorkMode = '3' and asd.Status='3' then datediff(second,starttime,endtime) else 0 end) as 事务与管理时长, 	SUM(Case when asd.reasoncode = '5' and asd.WorkMode = '3' and asd.Status='3' then datediff(second,starttime,endtime) else 0 end) as 帮助支持时长, 	SUM(Case when asd.reasoncode = '6' and asd.WorkMode = '3' and asd.Status='3' then datediff(second,starttime,endtime) else 0 end) as 开会时长, 	SUM(Case when asd.reasoncode = '7' and asd.WorkMode = '3' and asd.Status='3' then datediff(second,starttime,endtime) else 0 end) as 多媒体事务时长, 	SUM(Case when asd.reasoncode = '2' and asd.WorkMode = '3' and asd.Status='3' then datediff(second,starttime,endtime) else 0 end) as 小休时长 from Agent_Status_Detail_CTI asd WITH(NOLOCK) left join Staff s on s.AGENT_ID = asd.AgentID where asd.StartTime >= CONVERT(varchar(10),GETDATE(),120) group by asd.AgentID ) D on D.AgentID = T.Agent_ID LEFT JOIN ( SELECT CASE WHEN acs.chat_queue > 0 THEN 	qu.name ELSE 	rg.rolegroupdescription END AS 组,  s.agent_id 工号, s.staffname 姓名, acs.devicedn AS 分机号, st.stname 状态,datediff(s, statetime, GETDATE()) 状态持续时长 FROM	staff s LEFT JOIN agent_current_state acs ON acs.agentid = s.agent_id LEFT JOIN dn_list dl ON dl.dn = acs.devicedn LEFT JOIN queue qu ON acs.chat_queue = qu.id LEFT JOIN rolegroup rg ON rg.rolegroup_id = s.defaultrolegroup LEFT JOIN (	SELECT s.agent_id AS agentid,CASE WHEN acs.chat_status > 0 THEN	acs.chat_status + 200	WHEN acs.reasoncode > 0 AND acs.status = 3 AND acs.workmode = 3 THEN 150 + acs.reasoncode WHEN acs.status = 3 AND acs.workmode <> 4 THEN 133  WHEN acs.status = 3	AND acs.workmode = 4 THEN 134 WHEN acs.status > 0	AND acs.status <> 3 THEN 100 + acs.status WHEN acs.chat_status = 0 THEN acs.chat_status + 200 ELSE 999	END AS descstatus FROM staff s LEFT JOIN agent_current_state acs ON s.agent_id = acs.agentid LEFT JOIN rolegroup rg ON rg.rolegroup_id = s.defaultrolegroup WHERE 1 = 1 ) ns ON ns.agentid = acs.agentid LEFT JOIN status_translation st ON st.stid = ns.descstatus WHERE	1 = 1 AND s.workstatus = 'ONLINE' AND dl.type = 1 ) K ON T.AGENT_ID = K.[工号] LEFT JOIN (select ASD.AgentID, 	Case When Sum(Case When asd.Calltype = 1 And asd.Status = 3 And asd.Workmode = 4 Then 1 Else 0 End) = 0 Then 0 Else Sum(Case When asd.Calltype = 1 And asd.Status = 3 And asd.Workmode = 4 Then 	Datediff(Ss, asd.Starttime, asd.Endtime) Else 0 End) / Sum(Case When asd.Calltype = 1 And asd.Status = 3 And asd.Workmode = 4 Then 1 Else 0 End) End 平均话后时长 from Agent_Status_Detail_CTI ASD WITH(NOLOCK) LEFT JOIN Staff s on s.Agent_id = asd.AgentID LEFT JOIN STAFFROLEGROUP srg on srg.STAFF_ID = s.Staff_id  LEFT JOIN ROLEGROUP rg on rg.ROLEGROUP_ID = srg.ROLEGROUP_ID LEFT JOIN RolegroupArea rga on rga.Group_id = srg.ROLEGROUP_ID LEFT JOIN Group_Area ga on ga.Area_guid = rga.Area_guid  where asd.StartTime >= CONVERT(varchar(10),GETDATE(),120) group by  asd.AgentID) a1 ON a1.AgentID = T.AGENT_ID  LEFT JOIN ( select asd.AgentID, CASE WHEN sum(case when asd.status in (2,3,4) and vCID is not null then datediff(s,starttime,endtime) else 0 end ) = 0 THEN '0%' ELSE LEFT(round(SUM(Case when asd.reasoncode = '2' and asd.WorkMode = '3' and asd.Status='3' then datediff(second,starttime,endtime) else 0 end) * 1.0 / sum(case when asd.status in (2,3,4) and vCID is not null then datediff(s,starttime,endtime) else 0 end ),2)*100,5) + '%' END 小休率, 	LEFT(round(CASE WHEN sum(case when asd.status in (2,3,4) and vCID is not null then datediff(s,starttime,endtime) else 0 end ) = 0 THEN 0 ELSE 	(sum(case when asd.status = 4 and vCID is not null AND (CallType = 1 or calltype =4) then datediff(s,starttime,endtime) else 0 end ) + 	 SUM(Case when asd.WorkMode = '3' and asd.Status='3' and reasoncode is not null and reasoncode != 0 and reasoncode != 2 then datediff(second,starttime,endtime) else 0 end) +  	 sum(CASE WHEN asd.status = 3 and workmode = 4 then datediff(second,starttime,endtime) else 0 end)) *1.0 /  	sum(case when asd.status in (2,3,4) and vCID is not null then datediff(s,starttime,endtime) else 0 end ) *100 END,5),5) + '%' as 工作效率 from Agent_Status_Detail_CTI asd WITH(NOLOCK) left join Staff s on s.AGENT_ID = asd.AgentID LEFT JOIN ROLEGROUP rg on rg.ROLEGROUP_ID = s.DEFAULTROLEGROUP LEFT JOIN RolegroupArea rga on rga.group_id = rg.ROLEGROUP_ID LEFT JOIN Group_Area ga on ga.area_guid = rga.Area_guid where asd.StartTime >= CONVERT(varchar(10),GETDATE(),120) group by asd.AgentID ) A on A.AgentID = T.Agent_id LEFT JOIN ( SELECT staff_id,COUNT(*)转评价数  FROM WMSK_Statisfaction WITH(NOLOCK) where createddate>=CONVERT(varchar(10),GETDATE(),120) AND createddate is not null GROUP BY staff_id ) W1 ON W1.staff_id = T.Staff_id LEFT JOIN ( SELECT staff_id,COUNT(*)好评数  FROM WMSK_Statisfaction WITH(NOLOCK) where createddate>=CONVERT(varchar(10),GETDATE(),120) AND createddate is not null AND statisfaction1 in ('1','2') GROUP BY staff_id ) W2 ON W2.Staff_id = T.Staff_id ORDER BY (呼入总量+呼出总量) DESC";
			eliteBean.update(todaysql_ins1, "dataSource");

			
			resulterror.put("code", "1");
			resulterror.put("msg", "每日人员数据写入完成");
			return  resulterror;
			
		}catch(Exception e){
			resulterror.put("code", "-1");
			resulterror.put("msg", "接口异常！"+e);
			return  resulterror;
		}
	}
}